﻿<%@ WebHandler Language="C#" Class="MESDevicesReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Collections;

/**
 * Description: MES设备台账统计报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.8.10
 **/

public class MESDevicesReport : IHttpHandler
{
    public bool IsReusable { get { return false; } }
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDevicesData":
                        r = getDevicesData(jObject);//查询已存在设备
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "addDevice":
                        r = addDevice(jObject);//添加设备
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "deleteDevice":
                        r = deleteDevice(jObject);//删除设备
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "updateDevice":
                        r = updateDevice(jObject);//更新设备信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "saveDevicesImportData":
                        r = saveDevicesImportData(jObject);//保存设备台账导入数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "deleteDeviceBatch":
                        r = deleteDeviceBatch(jObject);//批量删除设备
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDeviceDetail":
                        r = getDeviceDetail(jObject);//查询某分厂在一段时间内的正在使用的设备信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportInUseDevicesInfo":
                        //超x天待检统计明细导出
                        exportInUseDevicesInfo(jObject);
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    Result getData(JObject jObject)
    {
        string sql = @"
SELECT m.factoryname,
       m.deviceqty,--MES机数量
       m.inuseqty,--在用数量
       m.unuseqty,--未用数量
       m.userate,--设备利用率
       m.loginempcount,--登录人数
       m.loginfrequency,--登录频次
       NVL(r.reportfrequency,0) reportfrequency,--报工频次
       CASE WHEN M.inuseqty <> 0 THEN ROUND((M.loginempcount) / (M.inuseqty),2) ELSE NULL END renjibi,--人/机比,
       CASE WHEN M.loginempcount <> 0 THEN ROUND((M.loginfrequency) / (M.loginempcount),2) ELSE NULL END renpinci,--人/频次
       CASE WHEN M.inuseqty  = 0 THEN NULL ELSE ROUND(NVL(r.reportfrequency,0) / M.inuseqty / 30,4) END baojirijun,--报/机（日均）
       CASE WHEN M.inuseqty  = 0 THEN NULL ELSE ROUND(M.loginempcount / M.inuseqty,2) END avglogin,--平均登录
       z3.biggerthanmed normaluse,--正常使用数
       ROUND(z3.biggerthanmed / m.deviceqty,4) * 100 validuserate--有效利用率
FROM (
    SELECT t.factoryname,COUNT(1) deviceqty,--MES机数量
           SUM(CASE WHEN lg.loginip IS NOT NULL THEN 1 ELSE 0 END) inuseqty,--在用数量
           COUNT(1) - SUM(CASE WHEN lg.loginip IS NOT NULL THEN 1 ELSE 0 END) unuseqty,--未用数量,
           ROUND(SUM(CASE WHEN lg.loginip IS NOT NULL THEN 1 ELSE 0 END) / COUNT(1),4) * 100 userate,
           NVL(SUM(lg2.loginempcount),0) loginempcount,--登录人数
           NVL(SUM(lg3.logincount),0) loginfrequency--登录频次
    FROM mes_devices_log t
    LEFT JOIN (
       SELECT u.loginip
       FROM userlogininfo u
       WHERE u.logindate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
             AND u.logindate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
       GROUP BY u.loginip
    ) lg ON lg.loginip = t.ip_address
    LEFT JOIN (
       SELECT t.loginip,COUNT(t.employeename) loginempcount
       FROM 
       (
         SELECT u.loginip,u.employeename
         FROM userlogininfo u
         WHERE u.logindate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
               AND u.logindate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
         GROUP BY u.loginip,u.employeename
       ) t
       GROUP BY t.loginip
    ) lg2 ON lg2.loginip = t.ip_address
    LEFT JOIN (
         SELECT u.loginip,COUNT(u.employeename) logincount
         FROM userlogininfo u
         WHERE u.logindate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
               AND u.logindate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
         GROUP BY u.loginip
    ) lg3 ON lg3.loginip = t.ip_address
    GROUP BY t.factoryname
) m
LEFT JOIN (
  SELECT f.factoryname,COUNT(csd.containersteprecordid) reportfrequency
  FROM containersteprecord csd
  LEFT JOIN factory f ON f.factoryid = csd.factoryid
  WHERE csd.reportdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
        AND csd.reportdate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
  GROUP BY f.factoryname
) r ON m.factoryname = r.factoryname
LEFT JOIN
(
    SELECT z2.factoryname,SUM(z2.biggerthanmed) biggerthanmed
    FROM 
    (
      SELECT t.factoryname,u.loginip,COUNT(u.employeename) logincount,zz.medcount,
             CASE WHEN COUNT(u.employeename) >= zz.medcount THEN 1 ELSE 0 END biggerthanmed
      FROM mes_devices_log t
      LEFT JOIN userlogininfo u ON t.ip_address = u.loginip
      LEFT JOIN (
           SELECT z.factoryname,MEDIAN(z.logincount) medcount
           FROM 
           (
             SELECT t.factoryname,u.loginip,COUNT(u.employeename) logincount
             FROM mes_devices_log t
             LEFT JOIN userlogininfo u ON t.ip_address = u.loginip
             WHERE u.logindate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
                   AND u.logindate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
             GROUP BY t.factoryname,u.loginip
           ) z
           GROUP BY z.factoryname
      ) zz ON zz.factoryname = t.factoryname
      WHERE u.logindate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
            AND u.logindate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
      GROUP BY t.factoryname,u.loginip,zz.medcount
    ) z2
    GROUP BY z2.factoryname
) z3 ON z3.factoryname = m.factoryname";
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        sql = string.Format(sql, selectdate[0].ToString(), selectdate[1].ToString());
        
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询数据
    Result getDevicesData(JObject jObject)
    {
        string sql = @"
SELECT t.id,t.device_no,t.factoryname,t.ip_address,e.fullname createemployee,t.createdate,e2.fullname lastchangeemployee,t.lastchangedate
FROM mes_devices_log t
LEFT JOIN employee e ON e.employeeid = t.createemployee
LEFT JOIN employee e2 ON e2.employeeid = t.lastchangeemployee
WHERE 1=1 ";
        //所属分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND t.factoryname = '{0}'", jObject["factoryname"].ToString());
        }
        //资产编号
        if (jObject["device_no"] != null && jObject["device_no"].ToString() != "")
        {
            sql += string.Format(" AND t.device_no = '{0}'", jObject["device_no"].ToString());
        }
        //ip地址
        if (jObject["ip_address"] != null && jObject["ip_address"].ToString() != "")
        {
            sql += string.Format(" AND t.ip_address = '{0}'", jObject["ip_address"].ToString());
        }

        sql += " ORDER BY t.device_no";
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //添加设备
    Result addDevice(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
INSERT INTO mes_devices_log(id,device_no,factoryname,ip_address,createemployee,createdate)
VALUES('{0}','{1}','{2}','{3}','{4}',sysdate)";

        string id = Guid.NewGuid().ToString();
        string device_no = jObject["device_no"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        string ip_address = jObject["ip_address"].ToString();
        string createemployee = jObject["employeeid"].ToString();

        sql = string.Format(sql, id, device_no, factoryname, ip_address, createemployee);
        int count = 0;
        try
        {
            count = dal.ExecuteSql(sql);
        }
        catch (Exception) { }
        r.data = new DataTable();
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //删除设备
    Result deleteDevice(JObject jObject)
    {
        string sql = string.Format("delete mes_devices_log t where t.id = '{0}'", jObject["id"].ToString());
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = new DataTable();
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //更新设备
    Result updateDevice(JObject jObject)
    {
        Result r = new Result();
        string sql = @" 
UPDATE mes_devices_log t SET t.device_no='{1}',t.factoryname = '{2}',t.ip_address = '{3}',t.lastchangeemployee = '{4}',t.lastchangedate = SYSDATE
WHERE t.id = '{0}'";
        string id = jObject["id"].ToString();
        string device_no = jObject["device_no"].ToString();
        string factoryname = jObject["factoryname"].ToString();
        string ip_address = jObject["ip_address"].ToString();
        string lastchangeemployee = jObject["employeeid"].ToString();
        sql = string.Format(sql, id, device_no, factoryname, ip_address, lastchangeemployee);
        int count = 0;
        try
        {
            count = dal.ExecuteSql(sql);
        }
        catch (Exception) { }
        r.data = new DataTable();
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //保存导入设备台账
    Result saveDevicesImportData(JObject jObject)
    {
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(jObject["willBeSavedData"].ToString());
        foreach (DataRow dr in dtPostData.Rows)
        {
            string sql = "SELECT * FROM mes_devices_log t WHERE t.device_no = '{0}'";
            sql = string.Format(sql, dr["device_no"].ToString());
            DataTable dt = dal.GetDataTable(sql);

            if (dt.Rows.Count > 0)
            {
                sql = @"
UPDATE mes_devices_log t 
SET t.factoryname = '{1}',t.ip_address = '{2}',t.lastchangeemployee = '{3}',t.lastchangedate = SYSDATE
WHERE t.device_no = '{0}'";
                sql = string.Format(sql,
                                    dr["device_no"].ToString(),
                                    dr["factoryname"].ToString(),
                                    dr["ip_address"].ToString(),
                                    jObject["employeeid"].ToString());
                dal.ExecuteSql(sql);
            }
            else
            {
                sql = @"
INSERT INTO mes_devices_log(id,device_no,factoryname,ip_address,createemployee,createdate)
VALUES('{0}','{1}','{2}','{3}','{4}',sysdate)";
                sql = string.Format(sql,
                                    Guid.NewGuid().ToString(),//唯一id
                                    dr["device_no"].ToString(),
                                    dr["factoryname"].ToString(),
                                    dr["ip_address"].ToString(),
                                    jObject["employeeid"].ToString());
                dal.ExecuteSql(sql);
            }
        }
        Result r = new Result();
        r.data = dtPostData;
        r.total = Convert.ToInt32(dtPostData.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //批量删除设备
    Result deleteDeviceBatch(JObject jObject)
    {
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(jObject["willBeDeleteData"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtPostData.Rows)
        {
            string sql = string.Format("delete mes_devices_log t where t.id = '{0}'", dr["id"].ToString());
            sqlList.Add(sql);
        }
        dal.ExecuteSqlTran(sqlList);
        Result r = new Result();
        r.data = new DataTable();
        r.total = Convert.ToInt32(dtPostData.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getDeviceDetail(JObject jObject)
    {
        string sql = @"
SELECT t.factoryname,t.device_no,t.ip_address
    FROM mes_devices_log t
    LEFT JOIN (
       SELECT u.loginip
       FROM userlogininfo u
       WHERE u.logindate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
             AND u.logindate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
       GROUP BY u.loginip
    ) lg ON lg.loginip = t.ip_address
WHERE t.factoryname = '{2}' ";
        if ((int)jObject["detailType"] == 1) {
            sql += " AND lg.loginip IS NOT NULL ";
        }
        else if ((int)jObject["detailType"] == 2)
        {
            sql += " AND lg.loginip IS NULL ";
        }
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        sql = string.Format(sql, selectdate[0].ToString(), selectdate[1].ToString(), jObject["factoryname"].ToString());

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    void exportInUseDevicesInfo(JObject jObject)
    {
        string sql = @"
SELECT t.factoryname,t.device_no,t.ip_address
    FROM mes_devices_log t
    LEFT JOIN (
       SELECT u.loginip
       FROM userlogininfo u
       WHERE u.logindate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') 
             AND u.logindate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
       GROUP BY u.loginip
    ) lg ON lg.loginip = t.ip_address
WHERE t.factoryname = '{2}' ";
        if ((int)jObject["detailType"] == 1)
        {
            sql += " AND lg.loginip IS NOT NULL ";
        }
        else if ((int)jObject["detailType"] == 2)
        {
            sql += " AND lg.loginip IS NULL ";
        }
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        sql = string.Format(sql, selectdate[0].ToString(), selectdate[1].ToString(), jObject["factoryname"].ToString());
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "分厂";
        dtResult.Columns["device_no"].Caption = "资产编号";
        dtResult.Columns["ip_address"].Caption = "IP";
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }
}